package com.dy.yunying.biz.dao.datacenter.impl;

import com.dy.yunying.api.dto.XingTuDataDTO;
import com.dy.yunying.api.vo.XingTuDataVO;
import com.dy.yunying.biz.config.YunYingProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.util.Collection;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 星图统计接口
 */
@Slf4j
@Component
public class XingTuDataDao {

	private static final DateTimeFormatter DATE_FORMATTER = new DateTimeFormatterBuilder().appendPattern("yyyyMMdd").toFormatter();

	@Resource(name = "clickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	@Resource
	private YunYingProperties yunYingProperties;

	/**
	 * 星图报表总数
	 *
	 * @param xingtu
	 * @return
	 */
	public int selectXingtuCount(XingTuDataVO xingtu) {
		final String countSqlStr = this.getCountSqlBuilder(xingtu).toString();
		log.info("星图统计报表总数SQL: \n[{}]", countSqlStr);
		final Integer count = clickhouseTemplate.queryForObject(countSqlStr, (row, idx) -> requireInteger(row.getObject(1)));
		return null == count ? 0 : count;
	}

	/**
	 * 星图报表分页
	 *
	 * @param xingtu 查询条件
	 * @param isPage 是否分页
	 * @return
	 */
	public List<XingTuDataDTO> selectXingtuPage(XingTuDataVO xingtu, boolean isPage) {
		final String listSqlStr = this.getPageSqlBuilder(xingtu, isPage).toString();
		log.info("星图统计报表分页SQL: \n[{}]", listSqlStr);
		return clickhouseTemplate.query(listSqlStr, xingtuObjectRelationMapping(xingtu));
	}

	private StringBuilder getCountSqlBuilder(XingTuDataVO xingtu) {
		final String cycleType = this.getCycleType(xingtu);
		final String deviceWhereString = this.getDeviceWhereString(xingtu);
		final String commonWhereString = this.getCommonWhereString(xingtu);
		final String groupBysStr = xingtu.getGroupBys().stream().map(e -> ", " + e).collect(Collectors.joining());

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT\n");
		sql.append("    COUNT(1)\n");
		sql.append("FROM\n");
		sql.append("    -- 星图任务订单相关关联统计\n");
		sql.append("    (\n");
		sql.append("        SELECT\n");
		sql.append("            0 AS comkey").append(groupBysStr).append('\n');
		sql.append("        FROM\n");
		sql.append("            (SELECT CAST(demand_id AS Nullable(UInt64)) AS demand_id, demand_name, advertiser_id, universal_settlement_type AS settle_type FROM v_odsmysql_xingtu_task WHERE deleted = 0) dem\n");
		sql.append("            LEFT JOIN (SELECT CAST(demand_id AS Nullable(UInt64)) AS demand_id1, CAST(order_id AS Nullable(UInt64)) order_id, CAST(author_id AS Nullable(UInt64)) AS author_id, author_name, title, CAST(item_id AS Nullable(UInt64)) AS item_id, head_image_uri, video_url, order_create_time AS online_time FROM v_odsmysql_xingtu_order WHERE deleted = 0) ord ON dem.demand_id = ord.demand_id1\n");
		sql.append("            LEFT JOIN (SELECT CAST(order_id AS Nullable(UInt64)) AS order_id1, chl_main AS parentchl, chl_sub AS chl, chl_base AS appchl, game_main AS pgid, game_sub AS gameid, os FROM ").append(yunYingProperties.getAdptypetable()).append(" WHERE ctype = 1 GROUP BY order_id1, chl_main, chl_sub, chl_base, game_main, game_sub, os) ap ON ord.order_id = ap.order_id1\n");
		sql.append("            LEFT JOIN (SELECT parent_code, chncode, dept_id, dept_name, dept_group_id, manage AS investor_id, real_name AS investor_name FROM v_dim_200_pangu_channel_user_dept_group) cudg ON ap.parentchl = cudg.parent_code AND ap.chl = cudg.chncode\n");
		sql.append("        WHERE\n");
		sql.append("            1 = 1").append(commonWhereString).append('\n');
		sql.append("        GROUP BY\n");
		sql.append("            comkey").append(groupBysStr).append('\n');
		sql.append("    ) dem\n");
		sql.append("    -- 关联设备数据\n");
		sql.append("    RIGHT JOIN (\n");
		sql.append("        SELECT\n");
		sql.append("            0 AS comkey, cycle_type").append(groupBysStr).append('\n');
		sql.append("        FROM\n");
		sql.append("            -- 设备归因数据统计查询\n");
		sql.append("            (\n");
		sql.append("                SELECT\n");
		sql.append("                    ").append(cycleType).append(groupBysStr).append('\n');
		sql.append("                FROM\n");
		sql.append("                    (\n");
		sql.append("                        SELECT\n");
		sql.append("                            kid, reg_day AS day, week, month, year, collect, settle_type, author_id, investor_id, chl_main AS parentchl, chl_base AS appchl, dept_id, game_main AS pgid, game_sub AS gameid, os\n");
		sql.append("                        FROM\n");
		sql.append("                            ").append(yunYingProperties.getNinetydeviceregtable()).append(" reg\n");
		sql.append("                            LEFT JOIN (SELECT parent_code, chncode, dept_id, dept_name, dept_group_id, manage AS investor_id, real_name AS investor_name FROM v_dim_200_pangu_channel_user_dept_group) cudg ON reg.chl_main = cudg.parent_code AND reg.chl_sub = cudg.chncode\n");
		sql.append("                            LEFT JOIN (SELECT CAST(order_id AS Nullable(UInt64)) AS order_id, chl_base AS chl FROM ").append(yunYingProperties.getAdptypetable()).append(" WHERE ctype = 1 GROUP BY order_id, chl_base) ap ON reg.chl_base = ap.chl\n");
		sql.append("                            LEFT JOIN (SELECT CAST(demand_id AS Nullable(UInt64)) AS demand_id, CAST(order_id AS Nullable(UInt64)) AS order_id, CAST(author_id AS Nullable(UInt64)) AS author_id, CAST(author_name AS Nullable(String)) AS author_name, CAST(item_id AS Nullable(UInt64)) AS item_id FROM v_odsmysql_xingtu_order WHERE deleted = 0) ord ON ap.order_id = ord.order_id\n");
		sql.append("                            LEFT JOIN (SELECT CAST(demand_id AS Nullable(UInt64)) AS demand_id, CAST(demand_name AS Nullable(String)) AS demand_name FROM v_odsmysql_xingtu_task WHERE deleted = 0) dem ON ord.demand_id=dem.demand_id\n");
		sql.append("                        WHERE\n");
		sql.append("                            spread_type = 2").append(deviceWhereString).append(commonWhereString).append('\n');
		sql.append("                    )\n");
		sql.append("                GROUP BY\n");
		sql.append("                    cycle_type").append(groupBysStr).append('\n');
		sql.append("            ) dev\n");
		sql.append("            -- 设备活跃数据统计查询\n");
		sql.append("            FULL JOIN (\n");
		sql.append("                SELECT\n");
		sql.append("                    ").append(cycleType).append(groupBysStr).append('\n');
		sql.append("                FROM\n");
		sql.append("                    (\n");
		sql.append("                        SELECT\n");
		sql.append("                            d_kid kid, active_day AS day, week, month, year, collect, d_settle_type AS settle_type, author_id, investor_id, d_chl_main AS parentchl, d_chl_base AS appchl, dept_id, d_game_main AS pgid, d_game_sub AS gameid, d_os AS os\n");
		sql.append("                        FROM\n");
		sql.append("                            ").append(yunYingProperties.getDeviceregisterlogintable()).append(" pay\n");
		sql.append("                            LEFT JOIN (SELECT parent_code, chncode, dept_id, dept_name, dept_group_id, manage AS investor_id, real_name AS investor_name FROM v_dim_200_pangu_channel_user_dept_group) cudg ON pay.d_chl_main = cudg.parent_code AND pay.d_chl_sub = cudg.chncode\n");
		sql.append("                            LEFT JOIN (SELECT CAST(order_id AS Nullable(UInt64)) AS order_id, chl_base AS chl FROM ").append(yunYingProperties.getAdptypetable()).append(" WHERE ctype = 1 GROUP BY order_id, chl_base) ap ON pay.d_chl_base = ap.chl\n");
		sql.append("                            LEFT JOIN (SELECT CAST(demand_id AS Nullable(UInt64)) AS demand_id, CAST(order_id AS Nullable(UInt64)) AS order_id, CAST(author_id AS Nullable(UInt64)) AS author_id, CAST(author_name AS Nullable(String)) AS author_name, CAST(item_id AS Nullable(UInt64)) AS item_id FROM v_odsmysql_xingtu_order WHERE deleted = 0) ord ON ap.order_id = ord.order_id\n");
		sql.append("                            LEFT JOIN (SELECT CAST(demand_id AS Nullable(UInt64)) AS demand_id, CAST(demand_name AS Nullable(String)) AS demand_name FROM v_odsmysql_xingtu_task WHERE deleted = 0) dem ON ord.demand_id=dem.demand_id\n");
		sql.append("                        WHERE\n");
		sql.append("                            d_spread_type = 2").append(deviceWhereString).append(commonWhereString).append('\n');
		sql.append("                    )\n");
		sql.append("                GROUP BY\n");
		sql.append("                    cycle_type").append(groupBysStr).append('\n');
		sql.append("            ) act USING (cycle_type").append(groupBysStr).append(")\n");
		sql.append("    ) dev USING (comkey").append(groupBysStr).append(")\n");
		return sql;
	}

	private StringBuilder getPageSqlBuilder(XingTuDataVO xingtu, boolean isPage) {
		final boolean afterShared = null != xingtu.getShowRatio() && 0 == xingtu.getShowRatio();
		final String cycleType = this.getCycleType(xingtu);
		final String deviceWhereString = this.getDeviceWhereString(xingtu);
		final String commonWhereString = this.getCommonWhereString(xingtu);
		final String[] requireGroupBysStrs = this.getGroupBysString(xingtu);
		final String groupBysStr1 = requireGroupBysStrs[0], groupBysStr2 = requireGroupBysStrs[1], groupBysStr3 = requireGroupBysStrs[2], groupBysStr4 = requireGroupBysStrs[3];
		final String orderByStr = this.getOrderBysString(xingtu);

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT\n");
		sql.append("    -- 分组字段\n");
		sql.append("    cycle_type").append(groupBysStr2).append(",\n");
		sql.append("    order_price, -- 订单价格\n");
		sql.append("    show_count, -- 展示数\n");
		sql.append("    click_count, -- 点击数\n");
		sql.append("    play_count, -- 播放数\n");
		sql.append("    finish_play_count, -- 完成播放数\n");
		sql.append("    fives_play_count, -- 有效播放数\n");
		sql.append("    comment_volume, -- 评论量\n");
		sql.append("    like_volume, -- 点赞量\n");
		sql.append("    play_volume, -- 播放量\n");
		sql.append("    share_volume, -- 分享量\n");
		sql.append("    -- 设备原始指标\n");
		sql.append("    device_count, -- 设备激活数\n");
		sql.append("    register_count, -- 新增设备注册数\n");
		sql.append("    new_pay_count, -- 新增设备付费数\n");
		sql.append("    total_pay_count, -- 累计充值人数\n");
		sql.append("    ").append(afterShared ? "new_pay_fee1" : "new_pay_fee2").append(" AS new_pay_fee, -- 新增充值实付金额\n");
		sql.append("    ").append(afterShared ? "new_pay_givemoney1" : "new_pay_givemoney2").append(" AS new_pay_givemoney, -- 新增充值代金券金额\n");
		sql.append("    ").append(afterShared ? "week_pay_fee1" : "week_pay_fee2").append(" AS week_pay_fee, -- 当周充值实付金额\n");
		sql.append("    ").append(afterShared ? "week_pay_givemoney1" : "week_pay_givemoney2").append(" AS week_pay_givemoney, -- 当周充值代金券金额\n");
		sql.append("    ").append(afterShared ? "month_pay_fee1" : "month_pay_fee2").append(" AS month_pay_fee, -- 当月充值实付金额\n");
		sql.append("    ").append(afterShared ? "month_pay_givemoney1" : "month_pay_givemoney2").append(" AS month_pay_givemoney, -- 当月充值代金券金额\n");
		sql.append("    ").append(afterShared ? "total_pay_fee1" : "total_pay_fee2").append(" AS total_pay_fee, -- 累计充值实付金额\n");
		sql.append("    ").append(afterShared ? "total_pay_givemoney1" : "total_pay_givemoney2").append(" AS total_pay_givemoney, -- 累计充值代金券金额\n");
		sql.append("    active_device_count, -- 活跃设备数\n");
		sql.append("    active_pay_count, -- 活跃设备付费数\n");
		sql.append("    ").append(afterShared ? "active_pay_fee1" : "active_pay_fee2").append(" AS active_pay_fee, -- 活跃充值实付金额\n");
		sql.append("    ").append(afterShared ? "active_pay_givemoney1" : "active_pay_givemoney2").append(" AS active_pay_givemoney, -- 活跃充值代金券金额\n");
		sql.append("    -- 任务二次计算指标\n");
		sql.append("    CASE WHEN click_count = 0 THEN 0 ELSE round(order_price / (CASE WHEN click_count = 0 THEN 100 ELSE click_count END), 2) END AS cost, -- 成本\n");
		sql.append("    CASE WHEN show_count = 0 THEN 0 ELSE round(click_count * 100 / (CASE WHEN show_count = 0 THEN 100 ELSE show_count END), 2) END AS click_rate, -- 点击率\n");
		sql.append("    CASE WHEN play_count = 0 THEN 0 ELSE round(order_price / 1000 / (CASE WHEN play_count = 0 THEN 100 ELSE play_count END), 2) END AS cpm_cost, -- 千次播放成本\n");
		sql.append("    CASE WHEN play_count = 0 THEN 0 ELSE round(finish_play_count * 100 / (CASE WHEN play_count = 0 THEN 100 ELSE play_count END), 2) END AS finish_rate, -- 完播率\n");
		sql.append("    CASE WHEN play_count = 0 THEN 0 ELSE round(fives_play_count * 100 / (CASE WHEN play_count = 0 THEN 100 ELSE play_count END), 2) END AS fives_play_rate, -- 有效播放率\n");
		sql.append("    -- 设备二次计算指标\n");
		sql.append("    CASE WHEN click_count = 0 THEN 0 ELSE round(device_count * 100 / (CASE WHEN click_count = 0 THEN 100 ELSE click_count END), 2) END AS click_device_rate, -- 点击激活率\n");
		sql.append("    CASE WHEN device_count = 0 THEN 0 ELSE round(register_count * 100 / (CASE WHEN device_count = 0 THEN 100 ELSE device_count END), 2) END AS device_register_rate, -- 激活注册率\n");
		sql.append("    CASE WHEN register_count = 0 THEN 0 ELSE round(order_price / (CASE WHEN register_count = 0 THEN 100 ELSE register_count END), 2) END AS device_cost, -- 设备成本\n");
		sql.append("    CASE WHEN register_count = 0 THEN 0 ELSE round(new_pay_fee / (CASE WHEN register_count = 0 THEN 100 ELSE register_count END), 2) END AS new_arpu, -- 新增ARPU\n");
		sql.append("    CASE WHEN register_count = 0 THEN 0 ELSE round(new_pay_count * 100 / (CASE WHEN register_count = 0 THEN 100 ELSE register_count END), 2) END AS new_pay_rate, -- 新增付费率\n");
		sql.append("    CASE WHEN active_device_count = 0 THEN 0 ELSE round(active_pay_fee / (CASE WHEN active_device_count = 0 THEN 100 ELSE active_device_count END), 2) END AS active_arpu, -- 活跃ARPU\n");
		sql.append("    CASE WHEN order_price = 0 THEN 0 ELSE round(new_pay_fee * 100 / (CASE WHEN order_price = 0 THEN 100 ELSE order_price END), 2) END AS first_day_roi, -- 首日ROI\n");
		sql.append("    CASE WHEN order_price = 0 THEN 0 ELSE round(week_pay_fee * 100 / (CASE WHEN order_price = 0 THEN 100 ELSE order_price END), 2) END AS first_week_roi, -- 当周ROI\n");
		sql.append("    CASE WHEN order_price = 0 THEN 0 ELSE round(month_pay_fee * 100 / (CASE WHEN order_price = 0 THEN 100 ELSE order_price END), 2) END AS first_month_roi, -- 当月ROI\n");
		sql.append("    CASE WHEN order_price = 0 THEN 0 ELSE round(total_pay_fee * 100 / (CASE WHEN order_price = 0 THEN 100 ELSE order_price END), 2) END AS total_pay_roi, -- 累计充值ROI\n");
		sql.append("    CASE WHEN device_count = 0 THEN 0 ELSE round(is_2_retention * 100 / IF(device_count = 0, 100, device_count), 2) END AS retention2 -- 次留\n");
		sql.append("FROM\n");
		sql.append("    -- 星图任务订单相关关联统计\n");
		sql.append("    (\n");
		sql.append("        SELECT\n");
		sql.append("            0 AS comkey").append(groupBysStr3).append(",\n");
		sql.append("            COALESCE(SUM(ROUND(order_price / 100, 2)), 0) AS order_price, -- 订单价格\n");
		sql.append("            COALESCE(SUM(show_count), 0) AS show_count, -- 展示数\n");
		sql.append("            COALESCE(SUM(click_count), 0) AS click_count, -- 点击数\n");
		sql.append("            COALESCE(SUM(play_count), 0) AS play_count, -- 播放数\n");
		sql.append("            COALESCE(SUM(finish_play_count), 0) AS finish_play_count, -- 完成播放数\n");
		sql.append("            COALESCE(SUM(fives_play_count), 0) AS fives_play_count, -- 有效播放数\n");
		sql.append("            COALESCE(SUM(comment_volume), 0) AS comment_volume, -- 评论量\n");
		sql.append("            COALESCE(SUM(like_volume), 0) AS like_volume, -- 点赞量\n");
		sql.append("            COALESCE(SUM(play_volume), 0) AS play_volume, -- 播放量\n");
		sql.append("            COALESCE(SUM(share_volume), 0) AS share_volume -- 分享量\n");
		sql.append("        FROM\n");
		sql.append("            (SELECT CAST(demand_id AS Nullable(UInt64)) AS demand_id, demand_name, advertiser_id, universal_settlement_type AS settle_type FROM v_odsmysql_xingtu_task WHERE deleted = 0) dem\n");
		sql.append("            LEFT JOIN (SELECT CAST(demand_id AS Nullable(UInt64)) AS demand_id1, CAST(order_id AS Nullable(UInt64)) order_id, CAST(author_id AS Nullable(UInt64)) AS author_id, author_name, title, CAST(item_id AS Nullable(UInt64)) AS item_id, head_image_uri, video_url, order_create_time AS online_time FROM v_odsmysql_xingtu_order WHERE deleted = 0) ord ON dem.demand_id = ord.demand_id1\n");
		sql.append("            LEFT JOIN (\n");
		sql.append("                SELECT\n");
		sql.append("                    CAST(order_id AS UInt64) AS order_id, CAST(price AS DECIMAL(18, 3)) AS order_price, CAST(convert_show AS Int32) show_count, CAST(convert_click AS Int32) AS click_count, CAST(play AS Int32) play_count,\n");
		sql.append("                    CAST(finish_number AS Int32) AS finish_play_count, CAST(five_s_play_number AS Int32) fives_play_count, CAST(spread_comment AS Int32) AS comment_volume, CAST(spread_like AS Int32) AS like_volume,\n");
		sql.append("                    CAST(spread_play AS Int32) play_volume, CAST(spread_share AS Int32) share_volume\n");
		sql.append("                FROM\n");
		sql.append("                    v_odsmysql_xingtu_order_detail\n");
		sql.append("                WHERE\n");
		sql.append("                    deleted = 0\n");
		sql.append("            ) ordd ON ord.order_id=ordd.order_id\n");
		sql.append("            LEFT JOIN (SELECT CAST(order_id AS Nullable(UInt64)) AS order_id1, chl_main AS parentchl, chl_sub AS chl, chl_base AS appchl, game_main AS pgid, game_sub AS gameid, os FROM ").append(yunYingProperties.getAdptypetable()).append(" WHERE ctype = 1 GROUP BY order_id1, chl_main, chl_sub, chl_base, game_main, game_sub, os) ap ON ord.order_id = ap.order_id1\n");
		sql.append("            LEFT JOIN (SELECT parent_code, chncode, dept_id, dept_name, dept_group_id, manage AS investor_id, real_name AS investor_name FROM v_dim_200_pangu_channel_user_dept_group) cudg ON ap.parentchl = cudg.parent_code AND ap.chl = cudg.chncode\n");
		sql.append("        WHERE\n");
		sql.append("            1 = 1").append(commonWhereString).append('\n');
		sql.append("        GROUP BY\n");
		sql.append("            comkey").append(groupBysStr3).append('\n');
		sql.append("    ) dem\n");
		sql.append("    -- 关联设备数据\n");
		sql.append("    RIGHT JOIN (\n");
		sql.append("        SELECT\n");
		sql.append("            0 AS comkey, cycle_type").append(groupBysStr4).append(",\n");
		sql.append("            device_count, -- 设备激活数\n");
		sql.append("            register_count, -- 新增设备注册数\n");
		sql.append("            new_pay_count, -- 新增付费设备数\n");
		sql.append("            total_pay_count, -- 累计充值设备数\n");
		sql.append("            new_pay_fee1, -- 新增充值实付金额（分成后）\n");
		sql.append("            new_pay_fee2, -- 新增充值实付金额（分成前）\n");
		sql.append("            new_pay_givemoney1, -- 新增充值代金券金额（分成后）\n");
		sql.append("            new_pay_givemoney2, -- 新增充值代金券金额（分成前）\n");
		sql.append("            week_pay_fee1, -- 当周充值实付金额（分成后）\n");
		sql.append("            week_pay_fee2, -- 当周充值实付金额（分成前）\n");
		sql.append("            week_pay_givemoney1, -- 当周充值代金券金额（分成后）\n");
		sql.append("            week_pay_givemoney2, -- 当周充值代金券金额（分成前）\n");
		sql.append("            month_pay_fee1, -- 当月充值实付金额（分成后）\n");
		sql.append("            month_pay_fee2, -- 当月充值实付金额（分成前）\n");
		sql.append("            month_pay_givemoney1, -- 当月充值代金券金额（分成后）\n");
		sql.append("            month_pay_givemoney2, -- 当月充值代金券金额（分成前）\n");
		sql.append("            total_pay_fee1, -- 累计充值实付金额（分成后）\n");
		sql.append("            total_pay_fee2, -- 累计充值实付金额（分成前）\n");
		sql.append("            total_pay_givemoney1, -- 累计充值代金券金额（分成后）\n");
		sql.append("            total_pay_givemoney2, -- 累计充值代金券金额（分成前）\n");
		sql.append("            is_2_retention, -- 次留数\n");
		sql.append("            active_device_count, -- 活跃设备数\n");
		sql.append("            active_pay_count, -- 活跃设备付费数\n");
		sql.append("            active_pay_fee1, -- 活跃充值实付金额（分成后）\n");
		sql.append("            active_pay_fee2, -- 活跃充值实付金额（分成前）\n");
		sql.append("            active_pay_givemoney1, -- 活跃充值代金券金额（分成后）\n");
		sql.append("            active_pay_givemoney2 -- 活跃充值代金券金额（分成前）\n");
		sql.append("        FROM\n");
		sql.append("            -- 设备归因数据统计查询\n");
		sql.append("            (\n");
		sql.append("                SELECT\n");
		sql.append("                    ").append(cycleType).append(groupBysStr4).append(",\n");
		sql.append("                    COUNT(DISTINCT kid) AS device_count, -- 设备激活数\n");
		sql.append("                    COALESCE(COUNT(IF(latest_username == '', NULL, latest_username)), 0) AS register_count, -- 新增设备注册数\n");
		sql.append("                    COALESCE(SUM(IF(fee_1 > 0 OR givemoney_1 > 0, 1, 0)), 0) AS new_pay_count, -- 新增付费设备数\n");
		sql.append("                    COALESCE(SUM(IF(fee_total > 0 OR givemoney_total > 0, 1, 0)), 0) AS total_pay_count, -- 累计充值设备数\n");
		sql.append("                    COALESCE(SUM(fee_1 * sharing), 0) AS new_pay_fee1, -- 新增充值实付金额（分成后）\n");
		sql.append("                    COALESCE(SUM(fee_1), 0) AS new_pay_fee2, -- 新增充值实付金额（分成前）\n");
		sql.append("                    COALESCE(SUM(givemoney_1 * sharing), 0) AS new_pay_givemoney1, -- 新增充值代金券金额（分成后）\n");
		sql.append("                    COALESCE(SUM(givemoney_1), 0) AS new_pay_givemoney2, -- 新增充值代金券金额（分成前）\n");
		sql.append("                    COALESCE(SUM(fee_week * sharing), 0) AS week_pay_fee1, -- 当周充值实付金额（分成后）\n");
		sql.append("                    COALESCE(SUM(fee_week), 0) AS week_pay_fee2, -- 当周充值实付金额（分成前）\n");
		sql.append("                    COALESCE(SUM(givemoney_week * sharing), 0) AS week_pay_givemoney1, -- 当周充值代金券金额（分成后）\n");
		sql.append("                    COALESCE(SUM(givemoney_week), 0) AS week_pay_givemoney2, -- 当周充值代金券金额（分成前）\n");
		sql.append("                    COALESCE(SUM(fee_month * sharing), 0) AS month_pay_fee1, -- 当月充值实付金额（分成后）\n");
		sql.append("                    COALESCE(SUM(fee_month), 0) AS month_pay_fee2, -- 当月充值实付金额（分成前）\n");
		sql.append("                    COALESCE(SUM(givemoney_month * sharing), 0) AS month_pay_givemoney1, -- 当月充值代金券金额（分成后）\n");
		sql.append("                    COALESCE(SUM(givemoney_month), 0) AS month_pay_givemoney2, -- 当月充值代金券金额（分成前）\n");
		sql.append("                    COALESCE(SUM(fee_total * sharing), 0) AS total_pay_fee1, -- 累计充值实付金额（分成后）\n");
		sql.append("                    COALESCE(SUM(fee_total), 0) AS total_pay_fee2, -- 累计充值实付金额（分成前）\n");
		sql.append("                    COALESCE(SUM(givemoney_total * sharing), 0) AS total_pay_givemoney1, -- 累计充值代金券金额（分成后）\n");
		sql.append("                    COALESCE(SUM(givemoney_total), 0) AS total_pay_givemoney2, -- 累计充值代金券金额（分成前）\n");
		sql.append("                    COALESCE(SUM(is_2_retention), 0) AS is_2_retention -- 次留数\n");
		sql.append("                FROM\n");
		sql.append("                    (\n");
		sql.append("                        SELECT\n");
		sql.append("                            kid, reg_day AS day, week, month, year, collect, settle_type, author_id, investor_id, investor_name, chl_main AS parentchl, chl_base AS appchl, dept_id, dept_name, game_main AS pgid, pgname, game_sub AS gameid, os,\n");
		sql.append("                            latest_username, is_2_retention, fee_1, givemoney_1, fee_week, givemoney_week, fee_month, givemoney_month, fee_total, givemoney_total, (1 - COALESCE(sharing, 0)) AS sharing\n");
		sql.append("                        FROM\n");
		sql.append("                            ").append(yunYingProperties.getNinetydeviceregtable()).append(" reg\n");
		sql.append("                            LEFT JOIN (SELECT CAST(id AS Int16) AS id, gname AS pgname, sharing FROM dim_200_pangu_mysql_parent_game) pg ON reg.game_main = pg.id\n");
		sql.append("                            LEFT JOIN (SELECT parent_code, chncode, dept_id, dept_name, dept_group_id, manage AS investor_id, real_name AS investor_name FROM v_dim_200_pangu_channel_user_dept_group) cudg ON reg.chl_main = cudg.parent_code AND reg.chl_sub = cudg.chncode\n");
		sql.append("                            LEFT JOIN (SELECT CAST(order_id AS Nullable(UInt64)) AS order_id, chl_base AS chl FROM ").append(yunYingProperties.getAdptypetable()).append(" WHERE ctype = 1 GROUP BY order_id, chl_base) ap ON reg.chl_base = ap.chl\n");
		sql.append("                            LEFT JOIN (SELECT CAST(demand_id AS Nullable(UInt64)) AS demand_id, CAST(order_id AS Nullable(UInt64)) AS order_id, CAST(author_id AS Nullable(UInt64)) AS author_id, CAST(author_name AS Nullable(String)) AS author_name, CAST(item_id AS Nullable(UInt64)) AS item_id FROM v_odsmysql_xingtu_order WHERE deleted = 0) ord ON ap.order_id = ord.order_id\n");
		sql.append("                            LEFT JOIN (SELECT CAST(demand_id AS Nullable(UInt64)) AS demand_id, CAST(demand_name AS Nullable(String)) AS demand_name FROM v_odsmysql_xingtu_task WHERE deleted = 0) dem ON ord.demand_id=dem.demand_id\n");
		sql.append("                        WHERE\n");
		sql.append("                            spread_type = 2").append(deviceWhereString).append(commonWhereString).append('\n');
		sql.append("                    )\n");
		sql.append("                GROUP BY\n");
		sql.append("                    cycle_type").append(groupBysStr4).append('\n');
		sql.append("            ) dev\n");
		sql.append("            -- 设备活跃数据统计查询\n");
		sql.append("            FULL JOIN (\n");
		sql.append("                SELECT\n");
		sql.append("                    ").append(cycleType).append(groupBysStr4).append(",\n");
		sql.append("                    COUNT(DISTINCT kid) AS active_device_count, -- 活跃设备数\n");
		sql.append("                    COALESCE(SUM(IF(fee > 0 OR givemoney > 0, 1, 0)), 0) AS active_pay_count, -- 活跃设备付费数\n");
		sql.append("                    COALESCE(SUM(fee * sharing), 0) AS active_pay_fee1, -- 活跃充值实付金额（分成后）\n");
		sql.append("                    COALESCE(SUM(fee), 0) AS active_pay_fee2, -- 活跃充值实付金额（分成前）\n");
		sql.append("                    COALESCE(SUM(givemoney * sharing), 0) AS active_pay_givemoney1, -- 活跃充值代金券金额（分成后）\n");
		sql.append("                    COALESCE(SUM(givemoney), 0) AS active_pay_givemoney2 -- 活跃充值代金券金额（分成前）\n");
		sql.append("                FROM\n");
		sql.append("                    (\n");
		sql.append("                        SELECT\n");
		sql.append("                            kid, active_day AS day, week, month, year, collect, d_settle_type AS settle_type, author_id, investor_id, investor_name, d_chl_main AS parentchl, d_chl_base AS appchl, dept_id, dept_name, d_game_main AS pgid, pgname, d_game_sub AS gameid, d_os AS os,\n");
		sql.append("                            fee, givemoney, (1 - COALESCE(sharing, 0)) AS sharing -- 分成比例\n");
		sql.append("                        FROM\n");
		sql.append("                            ").append(yunYingProperties.getDeviceregisterlogintable()).append(" pay\n");
		sql.append("                            LEFT JOIN (SELECT CAST(id AS Int16) AS id, gname AS pgname, sharing FROM dim_200_pangu_mysql_parent_game) pg ON pay.d_game_main = pg.id\n");
		sql.append("                            LEFT JOIN (SELECT parent_code, chncode, dept_id, dept_name, dept_group_id, manage AS investor_id, real_name AS investor_name FROM v_dim_200_pangu_channel_user_dept_group) cudg ON pay.d_chl_main = cudg.parent_code AND pay.d_chl_sub = cudg.chncode\n");
		sql.append("                            LEFT JOIN (SELECT CAST(order_id AS Nullable(UInt64)) AS order_id, chl_base AS chl FROM ").append(yunYingProperties.getAdptypetable()).append(" WHERE ctype = 1 GROUP BY order_id, chl_base) ap ON pay.d_chl_base = ap.chl\n");
		sql.append("                            LEFT JOIN (SELECT CAST(demand_id AS Nullable(UInt64)) AS demand_id, CAST(order_id AS Nullable(UInt64)) AS order_id, CAST(author_id AS Nullable(UInt64)) AS author_id, CAST(author_name AS Nullable(String)) AS author_name, CAST(item_id AS Nullable(UInt64)) AS item_id FROM v_odsmysql_xingtu_order WHERE deleted = 0) ord ON ap.order_id = ord.order_id\n");
		sql.append("                            LEFT JOIN (SELECT CAST(demand_id AS Nullable(UInt64)) AS demand_id, CAST(demand_name AS Nullable(String)) AS demand_name FROM v_odsmysql_xingtu_task WHERE deleted = 0) dem ON ord.demand_id=dem.demand_id\n");
		sql.append("                        WHERE\n");
		sql.append("                            d_spread_type = 2").append(deviceWhereString).append(commonWhereString).append('\n');
		sql.append("                    )\n");
		sql.append("                GROUP BY\n");
		sql.append("                    cycle_type").append(groupBysStr4).append('\n');
		sql.append("            ) act USING (cycle_type").append(groupBysStr4).append(")\n");
		sql.append("    ) dev USING (comkey").append(groupBysStr1).append(")\n");
		sql.append("ORDER BY\n");
		sql.append("    comkey DESC").append(orderByStr).append('\n');
		if (isPage) {
			sql.append("LIMIT\n");
			sql.append("    ").append(xingtu.offset()).append(", ").append(xingtu.getSize()).append('\n');
		}
		return sql;
	}

	private String getCycleType(XingTuDataVO xingtu) {
		String cycleType;
		if (null == xingtu.getCycleType()) {
			cycleType = "collect AS cycle_type";
		} else if (1 == xingtu.getCycleType()) {
			cycleType = "day AS cycle_type";
		} else if (2 == xingtu.getCycleType()) {
			cycleType = "week AS cycle_type";
		} else if (3 == xingtu.getCycleType()) {
			cycleType = "month AS cycle_type";
		} else {
			cycleType = "collect AS cycle_type";
		}
		return cycleType;
	}

	private String getDeviceWhereString(XingTuDataVO xingtu) {
		final StringBuilder deviceWhere = new StringBuilder();
		if (null != xingtu.getStartDate()) {
			deviceWhere.append(" AND day >= ").append(DATE_FORMATTER.format(xingtu.getStartDate()));
		}
		if (null != xingtu.getEndDate()) {
			deviceWhere.append(" AND day <= ").append(DATE_FORMATTER.format(xingtu.getEndDate()));
		}
		if (!xingtu.getIsAdmin() && !xingtu.getPrvInvestorIds().isEmpty()) {
			deviceWhere.append(" AND investor_id IN (").append(xingtu.getPrvInvestorIds().stream().map(String::valueOf).collect(Collectors.joining(", "))).append(')');
		}
		return deviceWhere.toString();
	}

	private String getCommonWhereString(XingTuDataVO xingtu) {
		final StringBuilder deviceWhere = new StringBuilder();
		if (StringUtils.isNotEmpty(xingtu.getDemandName())) {
			deviceWhere.append(" AND demand_name LIKE '%").append(xingtu.getDemandName()).append("%'");
		}
		if (StringUtils.isNotEmpty(xingtu.getAuthorName())) {
			deviceWhere.append(" AND author_name LIKE '%").append(xingtu.getAuthorName()).append("%'");
		}
		if (null != xingtu.getItemId()) {
			deviceWhere.append(" AND item_id = ").append(xingtu.getItemId());
		}
		if (CollectionUtils.isNotEmpty(xingtu.getPgids())) {
			deviceWhere.append(" AND pgid IN (").append(xingtu.getPgids().stream().map(String::valueOf).collect(Collectors.joining(", "))).append(')');
		}
		if (CollectionUtils.isNotEmpty(xingtu.getGameids())) {
			deviceWhere.append(" AND gameid IN (").append(xingtu.getGameids().stream().map(String::valueOf).collect(Collectors.joining(", "))).append(')');
		}
		if (StringUtils.isNotEmpty(xingtu.getParentchl())) {
			deviceWhere.append(" AND parentchl IN ('").append(xingtu.getParentchl().replaceAll(",", "','")).append("')");
		}
		if (CollectionUtils.isNotEmpty(xingtu.getAppchls())) {
			deviceWhere.append(" AND appchl IN ('").append(xingtu.getAppchls().stream().map(String::valueOf).collect(Collectors.joining("', '"))).append("')");
		}
		if (null != xingtu.getOs()) {
			deviceWhere.append(" AND os = ").append(xingtu.getOs());
		}
		if (CollectionUtils.isNotEmpty(xingtu.getDeptIds())) {
			deviceWhere.append(" AND dept_id IN (").append(xingtu.getDeptIds().stream().map(String::valueOf).collect(Collectors.joining(", "))).append(')');
		}
		if (CollectionUtils.isNotEmpty(xingtu.getUserGroupIds())) {
			deviceWhere.append(" AND dept_group_id IN (").append(xingtu.getUserGroupIds().stream().map(String::valueOf).collect(Collectors.joining(", "))).append(')');
		}
		if (CollectionUtils.isNotEmpty(xingtu.getInvestorIds())) {
			deviceWhere.append(" AND investor_id IN (").append(xingtu.getInvestorIds().stream().map(String::valueOf).collect(Collectors.joining(", "))).append(')');
		}
		return deviceWhere.toString();
	}

	private String[] getGroupBysString(XingTuDataVO xingtu) {
		final StringBuilder groupBysStr1 = new StringBuilder(), groupBysStr2 = new StringBuilder(), groupBysStr3 = new StringBuilder(), groupBysStr4 = new StringBuilder();

		final List<String> groupBys = xingtu.getGroupBys();
		for (String column : groupBys) {
			groupBysStr1.append(", ").append(column);
			groupBysStr2.append(", ").append(column);
			groupBysStr3.append(", ").append(column);
			groupBysStr4.append(", ").append(column);

			if ("author_id".equals(column)) {
				groupBysStr2.append(", author_name");
				groupBysStr3.append(", author_name");
			} else if ("investor_id".equals(column)) {
				groupBysStr2.append(", investor_name");
				groupBysStr4.append(", investor_name");
			} else if ("appchl".equals(column)) {
				groupBysStr2.append(", demand_id, demand_name, advertiser_id, settle_type, title, item_id, head_image_uri, video_url, online_time");
				groupBysStr3.append(", demand_id, demand_name, advertiser_id, settle_type, title, item_id, head_image_uri, video_url, online_time");
			} else if ("dept_id".equals(column)) {
				groupBysStr2.append(", dept_name");
				groupBysStr4.append(", dept_name");
			} else if ("pgid".equals(column)) {
				groupBysStr2.append(", pgname");
				groupBysStr4.append(", pgname");
			}
		}

		return new String[]{groupBysStr1.toString(), groupBysStr2.toString(), groupBysStr3.toString(), groupBysStr4.toString()};
	}

	private String getOrderBysString(XingTuDataVO xingtu) {
		final StringBuilder groupBysStr = new StringBuilder();

		if (StringUtils.isNotEmpty(xingtu.getOrderByName())) {
			groupBysStr.append(", ").append(xingtu.getOrderByName()).append(1 == ObjectUtils.defaultIfNull(xingtu.getOrderByAsc(), 0) ? " DESC" : " ASC");
		}

		groupBysStr.append(", cycle_type DESC");

		final List<String> groupBys = xingtu.getGroupBys();
		for (String column : groupBys) {
			groupBysStr.append(", ").append(column).append(" DESC");
		}

		return groupBysStr.toString();
	}

	private RowMapper<XingTuDataDTO> xingtuObjectRelationMapping(XingTuDataVO xingtu) {
		return (rs, idx) -> {
			final XingTuDataDTO queryBean = new XingTuDataDTO();
			Collection<String> groupBys = xingtu.getGroupBys();
			queryBean.setCycleType(rs.getString("cycle_type"));
			if (groupBys.contains("settle_type")) {
				queryBean.setSettleType(rs.getString("settle_type"));
			}
			if (groupBys.contains("author_id")) {
				queryBean.setAuthorId(requireLong(rs.getObject("author_id"))).setAuthorName(rs.getString("author_name"));
			}
			if (groupBys.contains("investor_id")) {
				queryBean.setInvestorId(requireLong(rs.getObject("investor_id"))).setInvestorName(rs.getString("investor_name"));
			}
			if (groupBys.contains("parentchl")) {
				queryBean.setParentchl(rs.getString("parentchl"));
			}
			if (groupBys.contains("appchl")) {
				queryBean.setAppchl(rs.getString("appchl"));
				queryBean.setAdvertiserId(rs.getString("advertiser_id"));
				queryBean.setDemandId(requireLong(rs.getObject("demand_id")));
				queryBean.setDemandName(rs.getString("demand_name"));
				queryBean.setTitle(rs.getString("title"));
				queryBean.setItemId(requireLong(rs.getObject("item_id")));
				queryBean.setHeadImageUri(rs.getString("head_image_uri"));
				queryBean.setVideoUrl(rs.getString("video_url"));
				queryBean.setOnlineTime(rs.getString("online_time"));
			}
			if (groupBys.contains("dept_id")) {
				queryBean.setDeptId(requireLong(rs.getObject("dept_id"))).setDeptName(rs.getString("dept_name"));
			}
			if (groupBys.contains("pgid")) {
				queryBean.setPgid(requireLong(rs.getObject("pgid"))).setPgname(rs.getString("pgname"));
			}
			if (groupBys.contains("gameid")) {
				queryBean.setGameid(requireLong(rs.getObject("gameid")));
			}
			if (groupBys.contains("os")) {
				queryBean.setOs(requireInteger(rs.getObject("os")));
			}
			queryBean.setCost(requireBigDecimal(rs.getBigDecimal("cost"), 2));
			queryBean.setClickCount(requireInteger(rs.getObject("click_count")));
			queryBean.setClickRate(requireBigDecimal(rs.getBigDecimal("click_rate"), 2));
			queryBean.setShowCount(requireInteger(rs.getObject("show_count")));
			queryBean.setCpmCost(requireBigDecimal(rs.getBigDecimal("cpm_cost"), 2));
			queryBean.setPlayCount(requireInteger(rs.getObject("play_count")));
			queryBean.setOrderPrice(requireBigDecimal(rs.getBigDecimal("order_price"), 2));
			queryBean.setFinishRate(requireBigDecimal(rs.getBigDecimal("finish_rate"), 2));
			queryBean.setFivesPlayRate(requireBigDecimal(rs.getBigDecimal("fives_play_rate"), 2));
			queryBean.setCommentVolume(requireInteger(rs.getObject("comment_volume")));
			queryBean.setLikeVolume(requireInteger(rs.getObject("like_volume")));
			queryBean.setPlayVolume(requireInteger(rs.getObject("play_volume")));
			queryBean.setShareVolume(requireInteger(rs.getObject("share_volume")));
			queryBean.setDeviceCount(requireInteger(rs.getObject("device_count")));
			queryBean.setClickDeviceRate(requireBigDecimal(rs.getBigDecimal("click_device_rate"), 2));
			queryBean.setRegisterCount(requireInteger(rs.getObject("register_count")));
			queryBean.setDeviceRegisterRate(requireBigDecimal(rs.getBigDecimal("device_register_rate"), 2));
			queryBean.setDeviceCost(requireBigDecimal(rs.getBigDecimal("device_cost"), 2));
			queryBean.setNewArpu(requireBigDecimal(rs.getBigDecimal("new_arpu"), 2));
			queryBean.setNewPayCount(requireInteger(rs.getObject("new_pay_count")));
			queryBean.setTotalPayCount(requireInteger(rs.getObject("total_pay_count")));
			queryBean.setNewPayRate(requireBigDecimal(rs.getBigDecimal("new_pay_rate"), 2));
			queryBean.setNewPayFee(requireBigDecimal(rs.getBigDecimal("new_pay_fee"), 2));
			queryBean.setNewPayGivemoney(requireBigDecimal(rs.getBigDecimal("new_pay_givemoney"), 2));
			queryBean.setActivePayFee(requireBigDecimal(rs.getBigDecimal("active_pay_fee"), 2));
			queryBean.setActivePayGivemoney(requireBigDecimal(rs.getBigDecimal("active_pay_givemoney"), 2));
			queryBean.setWeekPayFee(requireBigDecimal(rs.getBigDecimal("week_pay_fee"), 2));
			queryBean.setWeekPayGivemoney(requireBigDecimal(rs.getBigDecimal("week_pay_givemoney"), 2));
			queryBean.setMonthPayFee(requireBigDecimal(rs.getBigDecimal("month_pay_fee"), 2));
			queryBean.setMonthPayGivemoney(requireBigDecimal(rs.getBigDecimal("month_pay_givemoney"), 2));
			queryBean.setActiveArpu(requireBigDecimal(rs.getBigDecimal("active_arpu"), 2));
			queryBean.setActiveDeviceCount(requireInteger(rs.getObject("active_device_count")));
			queryBean.setFirstDayRoi(requireBigDecimal(rs.getBigDecimal("first_day_roi"), 2));
			queryBean.setFirstWeekRoi(requireBigDecimal(rs.getBigDecimal("first_week_roi"), 2));
			queryBean.setFirstMonthRoi(requireBigDecimal(rs.getBigDecimal("first_month_roi"), 2));
			queryBean.setTotalPayRoi(requireBigDecimal(rs.getBigDecimal("total_pay_roi"), 2));
			queryBean.setTotalPayFee(requireBigDecimal(rs.getBigDecimal("total_pay_fee"), 2));
			queryBean.setTotalPayGivemoney(requireBigDecimal(rs.getBigDecimal("total_pay_givemoney"), 2));
			queryBean.setRetention2(requireBigDecimal(rs.getBigDecimal("retention2"), 2));
			return queryBean;
		};
	}

	private static Integer requireInteger(Object value) {
		if (null == value) {
			return null;
		}
		if (value instanceof Number) {
			return ((Number) value).intValue();
		}
		throw new NumberFormatException(value.getClass() + " 不可转换为 java.lang.Integer");
	}

	private static Long requireLong(Object value) {
		if (null == value) {
			return null;
		}
		if (value instanceof Number) {
			return ((Number) value).longValue();
		}
		throw new NumberFormatException(value.getClass() + " 不可转换为 java.lang.Long");
	}

	private static BigDecimal requireBigDecimal(BigDecimal value, int scala) {
		if (null == value) {
			return null;
		}
		return value.setScale(scala, RoundingMode.HALF_UP);
	}

}
